﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;

namespace Npoi.Samples.CreateNewSpreadsheet
{

    public class ExcelExportet
    {
        #region 私有字段
        /// <summary>
        /// 类型和单元格格式的映射关系
        /// </summary>
        readonly Dictionary<Type, CellTypeEnum> _TypeMap = new Dictionary<Type, CellTypeEnum>()
        {
            { typeof(string), CellTypeEnum.常规},
            { typeof(bool), CellTypeEnum.常规 },
            { typeof(bool?), CellTypeEnum.常规},
            { typeof(DateTime), CellTypeEnum.时间},
            { typeof(DateTime?), CellTypeEnum.时间},
            { typeof(DateTimeOffset), CellTypeEnum.时间 },
            { typeof(DateTimeOffset?), CellTypeEnum.时间},
            { typeof(sbyte), CellTypeEnum.整数},
            { typeof(sbyte?), CellTypeEnum.整数},
            { typeof(byte), CellTypeEnum.整数 },
            { typeof(byte?), CellTypeEnum.整数},
            { typeof(short), CellTypeEnum.整数},
            { typeof(short?), CellTypeEnum.整数},
            { typeof(ushort), CellTypeEnum.整数},
            { typeof(ushort?), CellTypeEnum.整数},
            { typeof(int), CellTypeEnum.整数},
            { typeof(int?), CellTypeEnum.整数},
            { typeof(uint), CellTypeEnum.整数},
            { typeof(uint?), CellTypeEnum.整数},
            { typeof(long), CellTypeEnum.整数},
            { typeof(long?), CellTypeEnum.整数},
            { typeof(ulong), CellTypeEnum.整数},
            { typeof(ulong?), CellTypeEnum.整数},
            { typeof(float), CellTypeEnum.浮点数},
            { typeof(float?), CellTypeEnum.浮点数},
            { typeof(double), CellTypeEnum.浮点数},
            { typeof(double?), CellTypeEnum.浮点数},
            { typeof(decimal), CellTypeEnum.浮点数},
            { typeof(decimal?), CellTypeEnum.浮点数}
        };

        /// <summary>
        /// 单元格类型的默认格式化样式 
        /// </summary>
        readonly Dictionary<CellTypeEnum, string> _CellStyleStringFormatMap = new Dictionary<CellTypeEnum, string>()
        {
            { CellTypeEnum.日期, "yyyy年m月d日"},
            { CellTypeEnum.时间, "yyyy年m月d日 h:mm:ss"},
            { CellTypeEnum.整数, "0"},
            { CellTypeEnum.浮点数, "0.00"},
            { CellTypeEnum.百分比, "0.00%"},
            { CellTypeEnum.常规, "General"},
        };

        readonly Dictionary<CellTypeEnum, ICellStyle> _CellTypeStyleMap = new Dictionary<CellTypeEnum, ICellStyle>()
        {
            { CellTypeEnum.日期, default},
            { CellTypeEnum.时间, default},
            { CellTypeEnum.整数, default},
            { CellTypeEnum.浮点数, default},
            { CellTypeEnum.百分比, default},
            { CellTypeEnum.常规, default},
        };

        /// <summary>
        /// 是否导出所有列
        /// </summary>
        bool _IsShowAllHeader = true;

        /// <summary>
        /// 存储了 导出数据时允许导出的列
        /// </summary>
        Dictionary<string, ColHeaderInfo> _HeaderDic = new Dictionary<string, ColHeaderInfo>();

        #endregion

        public void SetStyle(CellTypeEnum style, string fmt)
        {
            if (!string.IsNullOrWhiteSpace(fmt))
            {
                _CellStyleStringFormatMap[style] = fmt;
            }
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="mList"></param>
        /// <param name="isShowAllHeader"></param>
        /// <param name="headerDic"></param>
        /// <returns></returns>
        public string SaveToExcel<T>(List<T> mList, string savePath = null, bool isShowAllHeader = true,
            Dictionary<string, ColHeaderInfo> headerDic = null) where T : class
        {
            if (mList == null) return "";
            #region 设置保存路径
            if (string.IsNullOrWhiteSpace(savePath))
                savePath = Path.Combine(Directory.GetCurrentDirectory(), Guid.NewGuid().ToString("N") + ".xlsx");
            else if (Path.GetDirectoryName(savePath).Length == 0)
                savePath = Path.Combine(Directory.GetCurrentDirectory(), savePath);
            Directory.CreateDirectory(Path.GetDirectoryName(savePath));
            if (File.Exists(savePath)) File.Delete(savePath);
            #endregion

            SetHeader<T>(isShowAllHeader, headerDic);

            using (var fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
            {
                #region 初始化类型样式键值对
                IWorkbook workbook = new XSSFWorkbook();
                var _dateStyle = workbook.CreateCellStyle();
                var _dataTimeStyle = workbook.CreateCellStyle();
                var _integerStyle = workbook.CreateCellStyle();
                var _doubleStyle = workbook.CreateCellStyle();
                var _rateStyle = workbook.CreateCellStyle();
                var _generalStyle = workbook.CreateCellStyle();

                _dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.日期]);
                _dataTimeStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.时间]);
                _integerStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.整数]);
                _doubleStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.浮点数]);
                _rateStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.百分比]);
                _generalStyle.DataFormat = workbook.CreateDataFormat().GetFormat(_CellStyleStringFormatMap[CellTypeEnum.常规]);

                _CellTypeStyleMap[CellTypeEnum.日期] = _dateStyle;
                _CellTypeStyleMap[CellTypeEnum.时间] = _dataTimeStyle;
                _CellTypeStyleMap[CellTypeEnum.整数] = _integerStyle;
                _CellTypeStyleMap[CellTypeEnum.浮点数] = _doubleStyle;
                _CellTypeStyleMap[CellTypeEnum.百分比] = _rateStyle;
                _CellTypeStyleMap[CellTypeEnum.常规] = _generalStyle;
                #endregion

                // 设置标题
                ISheet sheet1 = workbook.CreateSheet("Sheet1");
                IRow rowHeader = sheet1.CreateRow(0);
                rowHeader.Height = 350;
                var HeaderCellIdx = 0;
                //foreach (var item in _HeaderDic.OrderBy(t => t.Key))
                foreach (var item in _HeaderDic)
                {
                    var cell = rowHeader.CreateCell(HeaderCellIdx++);
                    cell.SetCellValue(item.Value.ShowName);
                }

                //属性按照名称排序
                //var propSortedList = TypeDescriptor.GetProperties(typeof(T)).Sort()
                //    .OfType<PropertyDescriptor>().ToList(); 
                var propSortedList = TypeDescriptor.GetProperties(typeof(T))
                    .OfType<PropertyDescriptor>().ToList();
                //设置表格数据
                for (int i = 0; i < mList.Count; i++)
                {
                    IRow row = sheet1.CreateRow(i + 1);
                    row.Height = 300;
                    int cIdx = 0;
                    foreach (var item in _HeaderDic)
                    {
                        var prop = propSortedList.First(t => t.Name == item.Key);
                        var cell = row.CreateCell(cIdx);
                        SetCellValue(cell, prop, mList[i], _HeaderDic[prop.Name]);
                        if (i == mList.Count - 1)
                        {
                            if (_HeaderDic[prop.Name].DataStyleEnum != null && _HeaderDic[prop.Name].DataStyleEnum.Value == CellTypeEnum.时间)
                            {
                                sheet1.SetColumnWidth(cIdx, 20 * 256);
                            }
                            else
                            {
                                sheet1.AutoSizeColumn(cIdx);
                            }
                        }
                        cIdx += 1;
                    }

                    //foreach (PropertyDescriptor prop in propSortedList)
                    //{
                    //    if (_HeaderDic.ContainsKey(prop.Name))
                    //    {
                    //        var cell = row.CreateCell(cIdx);

                    //        SetCellValue(cell, prop, mList[i], _HeaderDic[prop.Name]);
                    //        if (i == mList.Count - 1)
                    //        {
                    //            if (_HeaderDic[prop.Name].DataStyleEnum != null && _HeaderDic[prop.Name].DataStyleEnum.Value == CellTypeEnum.时间)
                    //            {
                    //                sheet1.SetColumnWidth(cIdx, 20 * 256);
                    //            }
                    //            else
                    //            {
                    //                sheet1.AutoSizeColumn(cIdx);
                    //            }
                    //        }
                    //        cIdx += 1;
                    //    }
                    //}
                }
                workbook.Write(fs);
                return savePath;
            }
            return "";
        }

        #region 私有方法

        //ToDo: nullable类型为Null判断, 强制转换能否成功
        private void SetCellValue<T>(ICell cell, PropertyDescriptor propDesc, T instance, ColHeaderInfo headerInfo)
        {
            var val = propDesc.GetValue(instance);
            if (val == null)
            {
                cell.CellStyle = _CellTypeStyleMap[CellTypeEnum.常规];
                cell.SetCellValue("");
                return;
            }

            var style = headerInfo.DataStyleEnum;
            if (style == null)
            {
                style = GetStyleByType(propDesc.PropertyType);
            }

            cell.CellStyle = _CellTypeStyleMap[style.Value];
            switch (style)
            {
                case CellTypeEnum.日期:
                    cell.SetCellValue((DateTime)val);

                    break;
                case CellTypeEnum.时间: cell.SetCellValue((DateTime)val); break;
                case CellTypeEnum.整数: cell.SetCellValue(Convert.ToInt64(val)); break;
                case CellTypeEnum.浮点数: cell.SetCellValue(Convert.ToDouble(val)); break;
                case CellTypeEnum.百分比: cell.SetCellValue(Convert.ToDouble(val)); break;
                case CellTypeEnum.常规:
                default: cell.SetCellValue(val.ToString()); break;
            }
        }

        CellTypeEnum GetStyleByType(Type t)
        {
            if (_TypeMap.ContainsKey(t))
            {
                return _TypeMap[t];
            }
            return CellTypeEnum.常规;
        }

        bool IsPrimitive(Type t)
        {
            if (_TypeMap.ContainsKey(t))
            {
                return true;
            }
            return false;
            //if (t.IsValueType || t.Name == "String")
            //{
            //    return CellTypeEnum.常规;
            //}
        }

        /// <summary>
        /// 设置需要导出的属性集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="isShowAllHeader"></param>
        /// <param name="headerDic"></param>
        void SetHeader<T>(bool isShowAllHeader = true, Dictionary<string, ColHeaderInfo> headerDic = null) where T : class
        {
            if (!isShowAllHeader && (headerDic == null || headerDic.Any()))
            {
                throw new Exception("如果开启不显示全部的表头,则必须传递自定义表头");
            }
            if (!isShowAllHeader)
            {
                if (headerDic != null) _HeaderDic = headerDic;
            }
            else
            {
                //生成默认的表头
                _HeaderDic = new Dictionary<string, ColHeaderInfo>();
                Type type = typeof(T);
                PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(type);
                foreach (PropertyDescriptor prop in properties)
                {
                    //只有值类型、字符串、日期类型的属性才导出，其他类型成员会忽略，如类，集合类
                    if (IsPrimitive(prop.PropertyType))
                    {
                        var style = GetStyleByType(prop.PropertyType);
                        _HeaderDic.Add(prop.Name, new ColHeaderInfo(prop.Name, style));
                    }
                }

                //使用指定的表头替换对应的默认表头
                if (headerDic != null)
                {
                    foreach (var hItem in headerDic)
                    {
                        if (_HeaderDic.ContainsKey(hItem.Key) && hItem.Value != null)
                        {
                            _HeaderDic[hItem.Key] = hItem.Value;
                        }
                    }
                }
            }
        }
        #endregion
    }

    public enum CellTypeEnum
    {
        日期,
        时间,
        整数,
        浮点数,
        百分比,
        常规
    }

    /// <summary>
    /// 表格信息
    /// </summary>
    public class ColHeaderInfo
    {
        public CellTypeEnum? DataStyleEnum { get; set; }
        public string ShowName { get; set; }

        public ColHeaderInfo(string name, CellTypeEnum e)
        {
            ShowName = name;
            DataStyleEnum = e;
        }
        public ColHeaderInfo(string name)
        {
            ShowName = name;
        }
    }



}
